﻿using Dapper;
using HongStone.Domain.AttendanceManagement;
using HongStone.Ince;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HongStone.Repository
{
    /// <summary>
    /// 考勤管理的数据访问层
    /// </summary>
    public class AttendanceRepository: AttendanceInce
    {

        private readonly HongStoneContext _app;
        public AttendanceRepository(HongStoneContext app)
        {
            _app = app;
        }

        /// <summary>
        /// 添加申请(请假)
        /// </summary>
        /// <returns></returns>
        public int AddClocking_inApplyFo(Clocking_inApplyFo model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"insert into Clocking_inApplyFo values('{model.ApplyName}','{model.ApplyForTpe}','{model.LeaveReason}','{model.EntryName}','{model.TravelLocation}','{model.BeginLeaveTime}','{model.FinishLeaveTime}','{model.StatisticsTime}','{model.ApplyForRemark}','{model.Clocking_inState}','{model.ZhuangTai}','{model.GongLi}')");

                return list;
            }
        }

        /// <summary>
        /// 显示待审批状态
        /// </summary>
        /// <returns></returns>
        public List<Clocking_inApplyFo> GetClocking_inApplyFo()
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Clocking_inApplyFo>($"select * from  Clocking_inApplyFo");

                return list.ToList();
            }
        }

        /// <summary>
        ///获取数据
        /// </summary>
        /// <param name="ApplyForId"></param>
        /// <returns></returns>
        public List<Clocking_inApplyFo> EditClocking_inApplyFo(int ApplyForId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Clocking_inApplyFo>($"select * from Clocking_inApplyFo where ApplyForId='{ApplyForId}'");

                return list.ToList();
            }
        }

        /// <summary>
        /// 修改状态（请假申请）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateQing01(int ApplyForId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update Clocking_inApplyFo set ZhuangTai='已处理',Clocking_inState='审批通过' where ApplyForId={ApplyForId}");

                return list;
            }
        }

        /// <summary>
        /// 修改状态（请假申请）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdateQing02(int ApplyForId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update Clocking_inApplyFo set ZhuangTai='未处理',Clocking_inState='审批不通过' where ApplyForId={ApplyForId}");

                return list;
            }
        }

        /// <summary>
        /// 批量删除（请假申请）
        /// </summary>
        /// <returns></returns>
        public int DeleteQing(string ids)
        {
            var list = _app.clocking_InApplyFos.Where(u => ids.Contains(Convert.ToString(u.ApplyForId)));
            _app.clocking_InApplyFos.RemoveRange(list);

            return _app.SaveChanges();
        }

        /// <summary>
        /// 请假申请列表
        /// </summary>
        /// <param name="ApplyName"></param>
        /// <param name="ShiJian"></param>
        /// <param name="Clocking_inState"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public List<Clocking_inApplyFo> GetQing(string ApplyName, string ShiJian, string Clocking_inState, int pageIndex, int pageSize, out int totalCount, string ZhuangTai,int ApplyForTpe)
        {

            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var sql = $"select * from Clocking_inApplyFo where 1=1 ";
                if (ApplyForTpe != 0)
                {
                    sql += $" and ApplyForTpe = {ApplyForTpe}";
                }
                if (!string.IsNullOrEmpty(ZhuangTai))
                {
                    sql += $" and ZhuangTai='{ZhuangTai}'";
                }
                if (!string.IsNullOrEmpty(ApplyName))
                {
                    sql += $" and ApplyName like '%{ApplyName}%'";
                }
                if (!string.IsNullOrEmpty(ShiJian))
                {
                    sql += $" and BeginLeaveTime like '{ShiJian}'";
                }
                if (!string.IsNullOrEmpty(Clocking_inState))
                {
                    sql += $" and Clocking_inState='{Clocking_inState}'";
                }

                var list = conn.Query<Clocking_inApplyFo>(sql).ToList();

                totalCount = list.Count();

                sql += $" order by ApplyForId offset {(pageIndex - 1) * pageSize} rows fetch next {pageSize} rows only";


                var page = conn.Query<Clocking_inApplyFo>(sql).ToList();

                return page;
            }
        }

        /// <summary>
        /// 反填（请假管理）
        /// </summary>
        /// <returns></returns>
        public Clocking_inApplyFo EditQing(int ApplyForId)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Query<Clocking_inApplyFo>($"select *from Clocking_inApplyFo where ApplyForId={ApplyForId}").FirstOrDefault();

                return list;
            }
        }

        /// <summary>
        /// 请假修改
        /// </summary>
        /// <returns></returns>
        public int UpdateQing(Clocking_inApplyFo model)
        {
            using (IDbConnection conn = new SqlConnection("Data Source=10.31.60.27;Initial Catalog=HongShiDB;User ID=sa;pwd=123456"))
            {
                var list = conn.Execute($"update Clocking_inApplyFo set  ApplyName='{model.ApplyName}',LeaveReason='{model.LeaveReason}',EntryName='{model.EntryName}',TravelLocation='{model.TravelLocation}',BeginLeaveTime='{model.BeginLeaveTime}',FinishLeaveTime='{model.FinishLeaveTime}',StatisticsTime='{model.StatisticsTime}', ApplyForRemark='{model.ApplyForRemark}',Clocking_inState='{model.Clocking_inState}',ZhuangTai='{model.ZhuangTai}' where ApplyForId={model.ApplyForId}");

                return list;
            }
        }
    }
}
